package com.dp.service.companymanage;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.Query;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.dp.pojo.JsonPage;
import com.dp.pojo.UserInfo;
import com.dp.service.impl3.BaseImpl;
import com.dp.util.DataMap;
import com.dp.util.KeyGenerator;
import com.dp.util.MD5;
import com.dp.vo.TRoles;
import com.dp.vo.TSpcode;

@Service
@Transactional
public class UserServiceImpl extends BaseImpl {
	
	/**
	 * Find all the spcode Entity
	 * @param hashMap
	 * @param firstrow
	 * @param maxrow
	 * @return List<NewsBlacklist>
	 */
	public List<UserInfo> findAllUser(DataMap hashMap, Integer firstrow, Integer maxrow){
		JsonPage jpage=(JsonPage) hashMap.get("jpage");
		UserInfo userone=(UserInfo) hashMap.get("user");
		String userids = (String)hashMap.get("userids");
		StringBuffer sqlS = new StringBuffer("select tu.employee_id,tu.user_id,te.sp_code,ts.spname,tr.role_name,tu.can_access," +
				"te.employee_name,te.job,te.employee_phone from t_user tu,t_spcode ts,t_roles tr,t_employee te where " +
				"tu.employee_id=te.employee_id and te.sp_code=ts.spcode and tu.role_id=tr.role_id");
		if(userids!=null){
			sqlS.append(" and tu.user_id in ("+userids+")");
		}
		if(userone!=null){
			if(userone.getUser_id()!=null&& userone.getUser_id().toString().trim().length() > 0){
				sqlS.append(" AND tu.user_id like '%" + userone.getUser_id().toString().trim() + "%'");
			}
			if(userone.getEmployee_name()!=null&& userone.getEmployee_name().toString().trim().length() > 0){
				sqlS.append(" AND te.employee_name like '%" + userone.getEmployee_name().toString().trim() + "%'");
			}
			if(userone.getJob()!=null&& userone.getJob().toString().trim().length() > 0){
				sqlS.append(" AND te.job like '%" + userone.getJob().toString().trim() + "%'");
			}
			if(userone.getEmployee_phone()!=null&&userone.getEmployee_phone().toString().trim().length() > 0){
				sqlS.append(" AND te.employee_phone like '%" + userone.getEmployee_phone().toString().trim() + "%'");
			}
		}
		
		if(jpage!=null){
			if(jpage.getSidx()!=null&&!"".equals(jpage.getSidx())){
				sqlS.append(" order by "+jpage.getSidx()+" "+jpage.getSord());
			}
		}
		
		if (firstrow != null && maxrow != null) {
			sqlS.append(" limit "+firstrow+","+maxrow);
		}
		List<UserInfo> result = new ArrayList<UserInfo>();;
		try {
			//this.init();
			sql=sqlS.toString();
			logger.info("findAllUser excute sql:"+sqlS.toString());
			List<Object[]> temp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:temp)
			{
				UserInfo one = new UserInfo();
				one.setEmployee_id(this.getString(o[0]));
				one.setUser_id(this.getString(o[1]));
				one.setSp_code(this.getString(o[2]));
				one.setSpname(this.getString(o[3]));
				one.setRole_name(this.getString(o[4]));
				one.setCan_access("true".equalsIgnoreCase(this.getString(o[5]))?1:0);
				one.setEmployee_name(this.getString(o[6]));
				one.setJob(this.getString(o[7]));
				one.setEmployee_phone(this.getString(o[8]));
				result.add(one);
			}
//			this.closeAll();
		} catch (Exception e) {
			logger.error("findAllUser error!!!"+e.getMessage());
			e.printStackTrace();
		}
//		finally{
////			this.closeAll();
//		}
		return result;
	}

	
	/**
	 * 获得所有用户总记录数
	 * @param hashMap
	 * @return Integer
	 */
	public int findAllUserCount(DataMap hashMap){
		int iCount = 0; 
		UserInfo userone=(UserInfo) hashMap.get("user");
		String userids = (String)hashMap.get("userids");
		StringBuffer sqlS = new StringBuffer("select count(tu.user_id) from t_user tu,t_spcode ts,t_roles tr,t_employee te " +
				"where tu.employee_id=te.employee_id and te.sp_code=ts.spcode and tu.role_id=tr.role_id");
		if(userids!=null){
			sqlS.append(" and tu.user_id in ("+userids+")");
		}
		if(userone!=null){
			if(userone.getUser_id()!=null&& userone.getUser_id().toString().trim().length() > 0){
				sqlS.append(" AND tu.user_id like '%" + userone.getUser_id().toString().trim() + "%'");
			}
			if(userone.getEmployee_name()!=null&& userone.getEmployee_name().toString().trim().length() > 0){
				sqlS.append(" AND te.employee_name like '%" + userone.getEmployee_name().toString().trim() + "%'");
			}
			if(userone.getJob()!=null&& userone.getJob().toString().trim().length() > 0){
				sqlS.append(" AND te.job like '%" + userone.getJob().toString().trim() + "%'");
			}
			if(userone.getEmployee_phone()!=null&&userone.getEmployee_phone().toString().trim().length() > 0){
				sqlS.append(" AND te.employee_phone like '%" + userone.getEmployee_phone().toString().trim() + "%'");
			}
		}
		sql=sqlS.toString();
		iCount=this.getOneInt();
//		try {
//			this.init();
//			logger.info("findAllUserCount excute sql:"+sqlS.toString());
//			this.rs1=stmt.executeQuery(sqlS.toString());
//			if(this.rs1.next())
//				iCount=rs1.getInt(1);
//			this.closeAll();
//		} catch (Exception e) {
//			logger.error("findAllUserCount fail !!!" + e.getMessage());
//			e.printStackTrace();
//		} finally {
//			this.closeAll();
//		}
		return iCount;
	}
	
	public List<TRoles> findAllRoles()
	{
		List<TRoles> queryList=null;
		StringBuffer sqlS = new StringBuffer("SELECT n FROM TRoles n where roleName != 'ROOT'");
		try {
			Query query = em.createQuery(sqlS.toString());
			logger.info("findAllRoles excute sql:"+sqlS.toString());
			queryList = query.getResultList();
		} catch (Exception e) {
			logger.error("findAllRoles error!!!"+e.getMessage());
			e.printStackTrace();
		}
		return queryList;
	}
	
	//not use
//	public List<TSpcode> findAllSp(){
//		StringBuffer sqlS = new StringBuffer("SELECT n FROM TSpcode n ");
//		List<TSpcode> queryList=null;
//		try {
//			this.initEM();
//			Query query = em.createQuery(sqlS.toString());
//			logger.info("findAllSp excute sql:"+sqlS.toString());
//			queryList = query.getResultList();
//			this.closeAll();
//		} catch (Exception e) {
//			logger.error("findAllSp error!!!"+e.getMessage());
//			e.printStackTrace();
//		}finally{
//			this.closeAll();
//		}
//		return queryList;
//	}
	
	
	public List<TSpcode> findAllSubOrganization(DataMap hashMap)
	{
		String spcode=(String)hashMap.get("spcode");
		String spcodeS=(String)hashMap.get("spcodeS");
		StringBuffer sqlS = new StringBuffer("SELECT n FROM TSpcode n ");
		if(spcode!=null){
			sqlS.append("where n.spcode='"+spcode+"'");
		}else if(spcodeS!=null){
			sqlS.append("where n.spcode in ("+spcodeS+")");
		}
		sqlS.append(" order by n.id ");
		List<TSpcode> l=new ArrayList<TSpcode>();
		try {
			Query query = em.createQuery(sqlS.toString());
			logger.info("findAllSubOrganization excute sql:"+sqlS.toString());
			l = query.getResultList();
			
		} catch (Exception e) {
			logger.error("findAllSubOrganization error!!!"+e.getMessage());
			e.printStackTrace();
		}
		
		return l;
	}
	
	public List<TSpcode> findAllSubOrganization()
	{
		StringBuffer sqlS = new StringBuffer("SELECT n FROM TSpcode n ");
		sqlS.append(" order by n.id ");
		
		List<TSpcode> result = new ArrayList<TSpcode>();
		try {
			Query query = em.createQuery(sqlS.toString());
			logger.info("findAllSubOrganization excute sql:"+sqlS.toString());
			result = query.getResultList();
		} catch (Exception e) {
			logger.error("findAllSubOrganization error!!!"+e.getMessage());
			e.printStackTrace();
		}
		
		return result;
	}
	
	/*public List<String> findAllOrgByEmpid(String empid)
	{
		StringBuffer sqlS = new StringBuffer("SELECT spcode FROM t_employeeright n where employee_id=:employee_id");
		List<String> reslut=null;
		try {
			Query query = emr.createNativeQuery(sqlS.toString());
			query.setParameter("employee_id", empid);
			logger.info("findAllOrgByEmpid excute sql:"+sqlS.toString());
			List<Object> l = query.getResultList();
			if(l.size()>0)
			{
				reslut = new ArrayList<String>(l.size());
				for (Object o : l) 
				{
					if (o != null) 
					{
						int j = 0;
						reslut.add((String)o);
					}
				}
			}
		} catch (Exception e) {
			logger.error("findAllOrgByEmpid error!!!"+e.getMessage());
			e.printStackTrace();
		}
		return reslut;
	}*/
	
	/*public boolean seTSpcodeToUser(String spchklist,String empid)
	{
		boolean t = true;
		this.sql = "delete from t_employeeright where employee_id='"+empid+"'";
		logger.info("seTSpcodeToUser excute sql1:"+sql);
		try {
			this.init();
			stmt.addBatch(sql);
			String[] spList = spchklist.split(",");
			for(String spcode : spList)
			{
				if(!"".equals(spcode))
				{
					this.sql = "insert into t_employeeright(employee_id,spcode) values('"+empid+"','"+spcode+"')";
					stmt.addBatch(sql);
				}
			}
			stmt.executeBatch();
			conn.commit();
			this.closeAll();
		} catch (Exception e) {
			t = false;
			logger.error("seTSpcodeToUser fail !!!" + e.getMessage());
			e.printStackTrace();
			if(conn!=null){
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				} 
			}
		}
		finally{this.closeAll(); }
		return t;
	}*/
	
	public boolean checkUserId(String userid)
	{
		boolean t = false;
		try {
//			this.init();
			sql=("select count(user_id) as totalrow from t_user where user_id='"+userid+"'");
//			rs1.next();
//			if(!"0".equals(rs1.getString(1))) 
				t= this.getOneInt()>0;
		}
		catch (Exception e) {
			logger.error("checkUserId fail !!!" + e.getMessage());
			e.printStackTrace();
		}
//		finally{this.closeAll(); }
		return t;
	}
	
	
	public int insertUser(UserInfo user,String spcodeS)
	{
		//0成功1失败 
		int t = 0;
		String user_id = this.ObjectToStringDB(user.getUser_id());
		String role_id = this.ObjectToStringDB(user.getRole_id());
		String user_pwd = this.ObjectToStringDB(MD5.StringMD5(user.getUser_pwd()));
		String can_access = this.ObjectToStringDB(user.getCan_access());
		//emp
		String employee_id = this.ObjectToStringDB(KeyGenerator.getInstance().getNextKeyValue("t_employee"));
		String sp_code = this.ObjectToStringDB(user.getSp_code());
		String employee_name = this.ObjectToStringDB(user.getEmployee_name());
		String employee_phone = this.ObjectToStringDB(user.getEmployee_phone());
		String employee_email = this.ObjectToStringDB(user.getEmployee_email());
		String job = this.ObjectToStringDB(user.getJob());
		String info = this.ObjectToStringDB(user.getInfo());
		
		List<String> subMenuList = new ArrayList<String>();
		
		try {
//			this.init();
			sql=("select submenu_id from t_role_menu where role_id="+role_id);
			List<String> temp=this.em.createNativeQuery(sql).getResultList();
			for(String o:temp)
			{
				String oo = this.ObjectToStringDB(o);
				subMenuList.add(oo);
			}
			this.sql = "insert into t_user(user_id,employee_id,role_id,ctrl_date,user_pwd,auth_tupe,can_access) values("+user_id+","+employee_id+","+role_id+",now(),"+user_pwd+",'WR',"+can_access+ ")";
			logger.info("insertUser excute sql1:"+sql);
//			stmt.addBatch(sql);
			this.em.createNativeQuery(sql).executeUpdate();
			this.sql = "insert into t_employee values("+employee_id+","+sp_code+","+employee_name+","+employee_phone+","+employee_email+
				",'Y',"+job+","+info+")";
			logger.info("insertUser excute sql2:"+sql);
//			stmt.addBatch(sql);
			this.em.createNativeQuery(sql).executeUpdate();
			if(subMenuList.size()>0)
			{
				for(String o:subMenuList)
				{
					this.sql = "insert into t_user_auth (submenu_id,user_id) values( "+o+","+user_id+")";
//					stmt.addBatch(sql);
					this.em.createNativeQuery(sql).executeUpdate();
				}
			}
			
			/*//关联机构，发通知内容时可发送的机构（组）
			if(spcodeS!=null){
				String[] spList = spcodeS.split(",");
				for(String spcode : spList)
				{
					if(!"".equals(spcode))
					{
						this.sql = "insert into t_employeeright(employee_id,spcode) values("+employee_id+","+spcode+")";
						stmt.addBatch(sql);
					}
				}
			}*/
//			
//			stmt.executeBatch();
//			conn.commit();
//			this.closeAll();
		} catch (Exception e) {
			logger.error("insertUser fail !!!" + e.getMessage());
			e.printStackTrace();
			t=1;
			
		}
//		finally{this.closeAll(); }
		return t;
	}
	
	public UserInfo getUesrByEmpId(String emp_id)
	{
		UserInfo one = new UserInfo();
		try {
//	        this.init();
	        this.sql="select tu.user_id,tu.employee_id,tu.role_id,tu.user_pwd,tu.can_access,te.sp_code,te.employee_name,te.employee_phone,te.employee_email,te.job,te.info from t_user tu,t_employee te where te.employee_id='"+emp_id+"' and tu.employee_id=te.employee_id";
	        List<Object[]> temp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:temp)
			{
				one.setUser_id(this.getString(o[0]));
				one.setEmployee_id(this.getString(o[1]));
				one.setRole_id(this.getString(o[2]));
				one.setUser_pwd(this.getString(o[3]));
//				Boolean canaccess = (Boolean)rs1.getBoolean(5);
//				one.setCan_access(canaccess==true?1:0);
				one.setSp_code(this.getString(o[5]));
				one.setEmployee_name(this.getString(o[6]));
				one.setEmployee_phone(this.getString(o[7]));
				one.setEmployee_email(this.getString(o[8]));
				one.setJob(this.getString(o[9]));
				one.setInfo(this.getString(o[10]));
			}
		}catch (Exception e) {
				logger.error("findChannelidS fail !!!" + e.getMessage());
				e.printStackTrace();
				
		}
	    return one;
	}
	
	public int updateUser(UserInfo user,String user_pwddd,String spcodeS)
	{
		//0成功1失败2已存在
		int t = 0;
		String user_id = this.ObjectToStringDB(user.getUser_id());
		String role_id = this.ObjectToStringDB(user.getRole_id());
		String user_pwd = this.ObjectToStringDB(MD5.StringMD5(user.getUser_pwd()));
		String can_access = this.ObjectToStringDB(user.getCan_access());
		//emp
		String employee_id = this.ObjectToStringDB(user.getEmployee_id());
		String sp_code = this.ObjectToStringDB(user.getSp_code());
		String employee_name = this.ObjectToStringDB(user.getEmployee_name());
		String employee_phone = this.ObjectToStringDB(user.getEmployee_phone());
		String employee_email = this.ObjectToStringDB(user.getEmployee_email());
		String job = this.ObjectToStringDB(user.getJob());
		String info = this.ObjectToStringDB(user.getInfo());
		
		List<String> subMenuList = new ArrayList<String>();
		try {
//			this.init();
			sql=("select submenu_id from t_role_menu where role_id="+role_id);
			List<String> temp=this.em.createNativeQuery(sql).getResultList();
			for(String o:temp)
			{
				String oo = this.ObjectToStringDB(o);
				subMenuList.add(oo);
			}
			this.sql = "update t_user set role_id="+role_id+",can_access="+can_access+" where user_id="+user_id;
			if(user_pwddd != null && !"".equals(user_pwddd))
				this.sql = "update t_user set role_id="+role_id+",user_pwd="+user_pwd+",can_access="+can_access+" where user_id="+user_id;
			
			logger.info("updateUser excute sql1:"+sql);
//			stmt.addBatch(sql);
			this.em.createNativeQuery(sql).executeUpdate();
			this.sql = "update t_employee set sp_code="+sp_code+",employee_name="+employee_name+",employee_phone="+employee_phone+
				",employee_email="+employee_email+",job="+job+",info="+info+" where employee_id="+employee_id;
			logger.info("updateUser excute sql2:"+sql);
//			stmt.addBatch(sql);
			this.em.createNativeQuery(sql).executeUpdate();
	
			if(subMenuList.size()>0)
			{
				this.sql = "delete from t_user_auth where user_id="+user_id;
//				stmt.addBatch(sql);
				this.em.createNativeQuery(sql).executeUpdate();
				for(String o:subMenuList)
				{
					this.sql = "insert into t_user_auth (submenu_id,user_id) values( "+o+","+user_id+")";
//					stmt.addBatch(sql);
					this.em.createNativeQuery(sql).executeUpdate();
				}
			}
			
			/*//关联机构，发通知内容时可发送的机构（组）
			this.sql = "delete from t_employeeright where employee_id='"+user.getEmployee_id()+"'";
			stmt.addBatch(sql);
			if(spcodeS!=null){
				String[] spList = spcodeS.split(",");
				for(String spcode : spList)
				{
					if(!"".equals(spcode))
					{
						this.sql = "insert into t_employeeright(employee_id,spcode) values('"+user.getEmployee_id()+"',"+spcode+")";
						stmt.addBatch(sql);
					}
				}
			}*/
			
			
		} catch (Exception e) {
			logger.error("insertUser fail !!!" + e.getMessage());
			e.printStackTrace();
	   }
		return t;
	}
	
    /**
	 * 查询一个机构的机构spcode及其下属机构所对应的所有spcode
	 * @param spcode
	 * @return
	 */
	public String findAllSpcodesBySpcode(String spcode) {
		String spcodeS = "";
		try {
//	        this.init();
//				sql="select t.spcode,t.upspcode from t_spcode t";
//			prep=conn.prepareStatement(sql);	
//			rs1=prep.executeQuery();
			List<TSpcode> tslist = this.em.createQuery("select n from TSpcode n").getResultList();
			TSpcode t_spcode = null;
//			while(rs1.next()){
//				t_spcode=new TSpcode();
//				t_spcode.setSpcode(rs1.getString("spcode"));
//				t_spcode.setUpspcode(rs1.getString("upspcode"));
//				tslist.add(t_spcode);
//			}	
			List<String> spcode_templist=new ArrayList<String>();
			spcode_templist = getSpcode_templist(tslist,spcode,spcode_templist);
			if(spcode_templist.size()>0){
	        	for(String spcode_temp:spcode_templist){
	        		spcodeS+="'"+spcode_temp+"',";
	        	}
	        	if(!"".equals(spcodeS)){
	        		spcodeS=spcodeS.substring(0, spcodeS.length()-1);
	        	}
			}
		 }catch (Exception e) {
				logger.error("findAllSpcodes fail !!!" + e.getMessage());
				e.printStackTrace();
				
		} finally {
//			this.closeAll();
		}
		if("".equals(spcodeS)){
			spcodeS="''";
		}
		return spcodeS;
	}
	
	private List<String> getSpcode_templist(List<TSpcode> tslist,String spcode_temp,List<String> spcode_templist){
		spcode_templist.add(spcode_temp);
		for(TSpcode ts:tslist){
			if(spcode_temp.equals(ts.getUpspcode())){
				getSpcode_templist(tslist,ts.getSpcode(),spcode_templist);
			}
		}
		return spcode_templist;
	}
	
//	 public  ArrayList getAllMUser(String userids) {
//		 sql="select * from t_user where 1=1 ";
//		 if(userids!=null){
//			 sql+=" and user_id in ("+userids+")";
//		 }
//		 sql+=" order by ctrl_date";
//		 
//        try {
////            return queryALLResultSet(sql);
//        return null;
//        } catch (Exception e) {
//            e.printStackTrace();
//
//            return null;
//        }
//	  }
	
	 
	public TRoles getRoleById(String roleId){
		String hql = "select t1 from TRoles  as t1 where  t1.roleId='"+roleId+"'";
		TRoles role = (TRoles) this.getSingleResult(hql);
		if(null != role){
			return role;
		}
		return null;

	}
	
	/*public Object getSingleResult(Query query) {
		Object o =null;
		try {
			o = query.getSingleResult();
		} catch (NoResultException ex) {
			return null;
		} catch (NonUniqueResultException ex) {
			o = query.getResultList();
			return ((List) o).get(0);
		}
		return o;
	}*/
	/*public static boolean StringIsNotNull(Object str) {
        boolean b = false;
        try {
            if (!str.equals("") && !str.equals("null")) {
                b = true;
            }
        } catch (Exception e) {
        }
        return b;
    }*/
//	private String ObjectToStringDB(Object obj) {
//        try {
//            String str = obj.toString().trim();
//            if (str.equals("") || str.equals("null")) {
//                return "null";
//            }
//            return "'" + str + "'";
//        } catch (Exception e) {
//            return "null";
//        }
//    }
}
